package com.searchWay.service;

import com.searchWay.model.Customer;
import com.searchWay.model.Order;
import com.utils.HibernateUtil;
import org.hibernate.*;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.StandardBasicTypes;

import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

public class BusinessService {
    private SessionFactory sessionFactory = new HibernateUtil().getSessionFactory();

    private void findAnyByQBC() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            Criteria criteria = session.createCriteria(Customer.class);

            Criterion criterion = Restrictions.like("name", "T%");
            Criterion criterion1 = Restrictions.eq("age", 21);
            criteria = criteria.add(criterion);
            criteria = criteria.add(criterion1);
            List result = criteria.list();

//            List result = session.createCriteria(Customer.class)
//                    .setFetchMode("orders", FetchMode.JOIN)
//                    .add(Restrictions.like("name", "T", MatchMode.START))
//                    .list();
//
            for (Object aResult : result) {
                Customer customer = (Customer) aResult;
                customer.getOrders().iterator();
                System.out.println(customer.toString());
            }

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }

    private void findAnyByHQL() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();

            String hql = "from Customer as c where c.name=:customerName and c.age = :customerAge";
            Query query = session.createQuery(hql);
            query.setString("customerName", "Tom");
            query.setInteger("customerAge", 21);

            List result = query.list();
            for (Object aResult : result) {
                Customer customer = (Customer) aResult;
                customer.getOrders().iterator();
                System.out.println(customer.toString());
            }

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }
    private void findAnyByQBE() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();

            Customer exampleCustomer = new Customer();
            exampleCustomer.setAge(25);
            List result = session.createCriteria(Customer.class).add(Example.create(exampleCustomer)).list();

            for (Object aResult : result) {
                Customer customer = (Customer) aResult;
                customer.getOrders().iterator();
                System.out.println(customer.toString());
            }

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }

    private void findAnyBySQL() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();

            String sql = "SELECT id , name , age  FROM customers WHERE name LIKE :customerName  AND age = :customerAge";
            Query query = session.createSQLQuery(sql).addEntity(Customer.class);
            query.setString("customerName", "T%");
            query.setInteger("customerAge", 21);
            List<Customer> result = query.list();

            for (Customer customer : result) {
                System.out.println(customer.toString());
            }

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }

    private void leftOuterFetch() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            //HQL
            List result = session.createQuery("from Customer c left join fetch c.orders o where c.name like 'T%' ").list();

            for (Object o : result) {
                Customer customer = (Customer) o;
                System.out.println(customer.toString());

            }
            //QBC
            List list = session.createCriteria(Customer.class).setFetchMode("orders", FetchMode.JOIN)
                    .add(Restrictions.like("name", "T", MatchMode.START)).list();
            for (Object o : list) {
                Customer customer = (Customer) o;
                System.out.println(customer.toString());
            }

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }

    private void leftOuter() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            //HQL
            List result = session.createQuery("from Customer c left join c.orders where c.name like 'T%'").list();
            for (Object aResult : result) {
                Object[] pair = (Object[]) aResult;
                Customer customer = (Customer) pair[0];
                System.out.println(customer.toString());
                Order order = (Order) pair[1];
                if (order != null) {
                    System.out.println(order.toString());
                }
            }
            //QBC

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }
    private void innerJoin() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            //HQL
            List result = session.createQuery("from Customer c inner  join c.orders o where c.name like 'T%'").list();

            for (Object aResult : result) {
                Object[] pair = (Object[]) aResult;
                Customer customer = (Customer) pair[0];
                Order order = (Order) pair[1];
            }
            //QBC仅检索出Customer对象
            List list = session.createCriteria(Customer.class).add(Restrictions.like("name", "T", MatchMode.START))
                    .createCriteria("orders").add(Restrictions.like("orderNumber", "T", MatchMode.START)).list();
            for (Object aList : list) {
                Customer customer = (Customer) aList;
                System.out.println(customer.toString());
                //若是延迟检索，以下代码会初始化Customer对象的Orders集合
                Iterator order = customer.getOrders().iterator();
                System.out.println(order.toString());
            }

            List list1 = session.createCriteria(Customer.class).createAlias("orders", "o")
                    .add(Restrictions.like("name", "T", MatchMode.START))
                    .add(Restrictions.like("o.orderNumber", "T", MatchMode.START))
                    .list();
            //QBC检索出Customer和Orders对象
            List list2 = session.createCriteria(Customer.class).createAlias("orders", "o")
                    .add(Restrictions.like("name", "T", MatchMode.START))
                    .add(Restrictions.like("o.orderNumber", "T", MatchMode.START))
                    .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
                    .list();

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }

    private void innerJoinFetch() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            //HQL 迫切内连接检索结果可能包含重复元素，通过HashSet过滤
            List result = session.createQuery("from Customer c inner join fetch c.orders o where c.name like 'T%'").list();
            HashSet set = new HashSet(result);
            for (Object o : set) {
                Customer customer = (Customer) o;
                System.out.println(customer.toString());
            }

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }

    private void innerJoinImplicitimplicit() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();

            List result = session.createQuery("from Order o where o.customer.name like 'T%'").list();
            for (Object o : result) {
                Order order = (Order) o;
                System.out.println(order.toString());
            }

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }

    private void localSQL() {
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            String sql = "SELECT * FROM customers";
            Query query = session.createSQLQuery(sql)
                    .addScalar("ID", StandardBasicTypes.LONG)
                    .addScalar("NAME", StandardBasicTypes.STRING)
                    .addScalar("AGE", StandardBasicTypes.INTEGER);

            List result = query.list();
            for (Object o : result) {
                Object[] row = (Object[]) o;
                long id = (long) row[0];
                String name = (String) row[1];
                int age = (int) row[2];
                System.out.println("ID: " + id + " ,NAME: " + name + " ,AGE: " + age);
            }

            String sql1 = "SELECT o.*, c.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.name = :name";
            Query query1 = session.createSQLQuery(sql1)
                    .addEntity("o", Order.class)
                    .addJoin("c", "o.customer")
                    .setParameter("name", "Tom");

            List list = query1.list();
            for (Object o : list) {
                Object[] row = (Object[]) o;
                Order order = (Order) row[0];
                Customer customer = (Customer) row[1];
            }

            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null) {
                tx.rollback();
            }
            throw e;
        } finally {
            session.close();
        }
    }

    private void test() {
//        findAnyByHQL();
//        findAnyByQBC();
//        findAnyByQBE();
//        findAnyBySQL();
//        leftOuterFetch();
//        leftOuter();
//        innerJoin();
//        innerJoinFetch();
//        innerJoinImplicitimplicit();
        localSQL();

        sessionFactory.close();
    }

    public static void main(String args[]) {
        new BusinessService().test();
    }
}
